Example 12-1
------------------------------------------------------------------------------------------------
Update tblCustomers 
		Set Activate=0
		Where DateDiff(m,JoinDate,getdate())>6
		And CustomerID Not In(
			Select Distinct CustomerID 
			From tblInvoices)


Example 12-2
------------------------------------------------------------------------------------------------
USE msdb
sp_add_job @job_name ='ActivateCustomer',
   @enabled = 1,
   @description = 'Activate to 1',
   @owner_login_name = 'sa'
 

Example 12-3
------------------------------------------------------------------------------------------------
Update tblCustomers 
		Set Activate=1
		Where DateDiff(m,JoinDate,getdate())>6
		And CustomerID In(
			Select Distinct CustomerID 
			From tblInvoices)
  Where Activate=0


Example 12-4
------------------------------------------------------------------------------------------------
USE msdb
EXEC sp_add_jobstep @job_name = 'ActivateCustomer',
   @step_name = 'Set Activate to 1',
   @subsystem = 'TSQL',
   @command = 'Update tblCustomers 
		Set Activate=1
		Where DateDiff(m,JoinDate,getdate())>6
		And CustomerID In(
			Select Distinct CustomerID 
			From tblInvoices)
		Where Activate=0',
   @retry_attempts = 5,
   @retry_interval = 5,
   @database=mydatabase


Example 12-5
------------------------------------------------------------------------------------------------
USE msdb
EXEC sp_update_jobstep @job_name = 'ActivateCustomer', @step_id = 1,
   @step_name = 'SetActivateTo1'


Example 12-6
------------------------------------------------------------------------------------------------
USE msdb
EXEC sp_delete_jobstep @job_name = 'ActivateCustomer', 
   @step_id = 1


Example 12-7
------------------------------------------------------------------------------------------------
USE msdb
EXEC sp_update_job @job_name = 'ActivateCustomer',
   @new_name = 'ActivateCustomers',
   @description = 'Job disabled until end of project',
   @enabled = 0

Example 12-8
------------------------------------------------------------------------------------------------
USE msdb
EXEC sp_delete_job @job_name = 'ActivateCustomers'



Example 12-9
------------------------------------------------------------------------------------------------
USE msdb
EXEC sp_start_job @job_name = 'ActivateCustomer'

Example 12-10
------------------------------------------------------------------------------------------------
USE msdb
EXEC sp_sop_job @job_name = 'ActivateCustomer'

Example 12-11
------------------------------------------------------------------------------------------------

USE msdb
EXEC sp_add_jobschedule @job_name = 'ActivateCustomer', 
   @name = 'ScheduledExecution',
   @freq_type = 4, 
   @freq_interval = 1,
   @active_start_time = 10000


Example 12-12
------------------------------------------------------------------------------------------------
USE msdb
EXEC sp_update_jobschedule 
@job_name = 'ActivateCustomer', 
   @name = 'ScheduledExecution',
   @freq_type = 4, 
   @freq_interval = 2,
   @active_start_time = 30000


Example 12-13
------------------------------------------------------------------------------------------------
USE msdb
EXEC sp_delete_jobschedule 
   @job_name = N'ActivateCustomer', 
   @name = N'ScheduledExecution'


Example 12-14
------------------------------------------------------------------------------------------------
use msdb
exec sp_add_operator @name = 'RecruitVietnam',
   @enabled = 1,
   @email_address ='info@RecruitVietnam.com'

Example 12-15
------------------------------------------------------------------------------------------------
sp_add_notification 
	@alert_name = N'DeactivateCuastomerAlert', 
	@operator_name = N'RecruitVietnam', 
	@notification_method = 1

Example 12-16
------------------------------------------------------------------------------------------------
sp_update_notification 
	@alert_name = N'DeactivateCuastomerAlert', 
	@operator_name = N'RecruitVietnam', 
	@notification_method = 3



Example 12-17
------------------------------------------------------------------------------------------------
use msdb
exec sp_update_operator 
	@name = 'RecruitVietnam',
	@new_name = 'RecruitVietnam.Com',
   @enabled = 1,
   @email_address ='info@RecruitVietnam.com'

Example 12-18
------------------------------------------------------------------------------------------------
use msdb
exec sp_delete_operator 
@name =  'RecruitVietnam.Com' , 
@reassign_to_operator =  'HuuKhang' 

Example 12-19
------------------------------------------------------------------------------------------------
USE msdb
EXEC sp_add_alert 
	@name = 'myDatabaseBackupAlert', 
	@message_id = 0, 
	@event_description_keyword='Alert from Backup myDatabase Event' ,
	@severity = 001, 
	@database_name = N'mydatabase',
	@notification_message = 'Error of Backup Event Alert has occurred. The database will be backed up...', 
	@job_name = 'myDatabase Backup'

Example 12-20
------------------------------------------------------------------------------------------------
USE msdb
EXEC sp_add_notification 
	@alert_name = N'myDatabaseBackupAlert', 
	@operator_name = N'HuuKhang', 
	@notification_method = 1 

Example 12-21
------------------------------------------------------------------------------------------------
USE msdb
EXEC sp_update_alert 
	@name = 'myDatabaseBackupAlert', 
	@message_id = 0, 
	@event_description_keyword='Message from Backup myDatabase Event' ,
	@severity = 001, 
	@database_name = N'mydatabase',
	@notification_message = 'Error of Backup Event Alert has occurred. The database will be backed up...', 
	@job_name = 'myDatabase Backup'

Example 12-22
------------------------------------------------------------------------------------------------
USE msdb
EXEC sp_delete_alert  @name = 'myDatabaseBackupAlert'



